"""
Case Type   : fullsql
Case Name   : 验证事务commit下锁模块信息
Create At   : 2022/01/28
Owner       : zhao-pengcheng-g
Description :
    1.设置track_stmt_stat_level为'l2,l2'
    2.创建表，插入数据
    3.会话1开启事务,更新表数据
    4.会话2对同一张表的数据做更新操作
    5.新开会话中查询unique_sql_id
    6.查询lock_wait_count和lock_wait_time大于0
    7.环境清理
Expect      :
    1.设置成功
    2.成功
    3.成功
    4.成功
    5.成功
    6.成功
    7.环境清理成功
History     :
"""
import time
import unittest
from yat.test import Node
from yat.test import macro
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from testcase.utils.Common import Common
from testcase.utils.ComThread import ComThread


class Fullsql(unittest.TestCase):
    def setUp(self):
        self.logger = Logger()
        text = '---Opengauss_Function_Fullsql_Case0009 start---'
        self.logger.info(text)
        self.userNode = Node('PrimaryDbUser')
        self.sh_primy = CommonSH('PrimaryDbUser')
        self.constant = Constant()
        self.common = Common()
        self.table_name = 'table_fullsql_0009'

        text = '---设置track_stmt_session_slot为10000,expect:设置成功 ---'
        self.logger.info(text)
        self.config_slot = 'track_stmt_session_slot'
        self.show_slot_cmd = f'show {self.config_slot};'
        check_slot = self.sh_primy.execut_db_sql(self.show_slot_cmd)
        self.logger.info(check_slot)
        self.slot_default = check_slot.splitlines()[-2].strip()
        if '10000' != self.slot_default:
            guc_slot = self.sh_primy.execute_gsguc('reload',
                                                   self.constant.GSGUC_SUCCESS_MSG,
                                                   f'{self.config_slot}=10000')
            self.assertTrue(guc_slot, '执行失败:' + text)
        chk_slot_msg = self.sh_primy.execut_db_sql(self.show_slot_cmd)
        self.logger.info(chk_slot_msg)
        self.common.equal_sql_mdg(chk_slot_msg, self.config_slot, '10000',
                                  '(1 row)', flag='1')

    def test_fullsql(self):
        text = "---step1:设置track_stmt_stat_level为'l2,l2',expect:成功---"
        self.logger.info(text)
        self.config_level = 'track_stmt_stat_level'
        self.show_level_cmd = f'show {self.config_level};'
        show_level_res = self.sh_primy.execut_db_sql(self.show_level_cmd)
        self.logger.info(show_level_res)
        self.level_default = show_level_res.splitlines()[-2].strip()
        if 'l2,l2' != self.level_default:
            result = self.sh_primy.execute_gsguc('reload',
                                                 self.constant.GSGUC_SUCCESS_MSG,
                                                 f"{self.config_level}='l2,l2'")
            self.assertTrue(result, '参数修改失败')
        check_level_msg = self.sh_primy.execut_db_sql(self.show_level_cmd)
        self.logger.info(check_level_msg)
        self.common.equal_sql_mdg(check_level_msg, f'{self.config_level}',
                                  'l2,l2', '(1 row)', flag='1')

        text = '---step2:创建表，插入数据，expect:成功---'
        self.logger.info(text)
        sql_cmd = f'''select reset_unique_sql('global', 'all', 1);
                      set current_schema to public;
                      create table {self.table_name}
                      (id int,occupyamt number,damt number);
                      insert into {self.table_name} \
                      values(generate_series(1,10), 10.1, 10.1);'''
        sql_msg = self.sh_primy.execut_db_sql(sql_cmd)
        self.logger.info(sql_msg)
        self.assertTrue('reset_unique_sql' in sql_msg.splitlines()[0]
                        and 't' in sql_msg.splitlines()[2]
                        and '(1 row)' in sql_msg.splitlines()[3],
                        '执行失败:' + text)
        self.assertIn(self.constant.SET_SUCCESS_MSG and
                      self.constant.DROP_TABLE_SUCCESS and
                      self.constant.CREATE_TABLE_SUCCESS and
                      self.constant.INSERT_SUCCESS_MSG,
                      sql_msg, '执行失败:' + text)

        text_3 = '---step3:会话1开启事务,更新表数据,expect:成功---'
        self.logger.info(text_3)
        update_cmd = f'''start transaction;
                         select sysdate;
                         update {self.table_name} set 
                         {self.table_name}.occupyamt = 111 
                         where {self.table_name}.id = 3;
                         select sysdate;
                         select pg_sleep(100);
                         select sysdate;
                         select occupyamt from {self.table_name} where id =3;
                         end;'''
        self.logger.info(update_cmd)
        session1 = ComThread(self.sh_primy.execut_db_sql, args=(update_cmd,))
        session1.setDaemon(True)

        text_4 = '---step4:会话2对同一张表的数据做更新操作,expect:成功---'
        self.logger.info(text_4)
        update_cmd_4 = f'''select pg_sleep(10);
                        select sysdate;
                        update {self.table_name} 
                        set {self.table_name}.occupyamt = 222 
                        where {self.table_name}.id = 3;
                        select sysdate;
                        select occupyamt from {self.table_name} where id =3;'''
        self.logger.info(update_cmd_4)
        session2 = ComThread(self.sh_primy.execut_db_sql,
                             args=(update_cmd_4,))
        session2.setDaemon(True)

        session1.start()
        session2.start()
        session1.join(120)
        session2.join(120)
        result1 = session1.get_result()
        self.logger.info(result1)
        result2 = session2.get_result()
        self.logger.info(result2)
        self.assertIn(self.constant.START_TRANSACTION_SUCCESS_MSG
                      and self.constant.UPDATE_SUCCESS_MSG
                      and self.constant.COMMIT_SUCCESS_MSG,
                      result1, '执行失败:' + text_3)
        self.assertIn(self.constant.UPDATE_SUCCESS_MSG,
                      result2, '执行失败:' + text_4)

        text = '---step5:新开会话中查询unique_sql_id,expect:成功---'
        self.logger.info(text)
        sql_cmd = f'''select pg_sleep(60);
            select unique_sql_id from dbe_perf.statement 
            where query like '%update {self.table_name}%';'''
        sql_msg = self.sh_primy.execut_db_sql(sql_cmd, dbname='postgres')
        self.logger.info(sql_msg)
        unique_sql_id = sql_msg.splitlines()[-2].strip()
        self.assertTrue('unique_sql_id' in sql_msg.splitlines()[-4]
                        and '(1 row)' in sql_msg.splitlines()[-1],
                        '执行失败:' + text)

        text = '--step6:查询lock_wait_count和lock_wait_time大于0,expect:成功--'
        self.logger.info(text)
        sql_cmd = f'''select pg_sleep(30);
            select lock_wait_count>0,lock_wait_time>0 
            from dbe_perf.get_global_full_sql_by_timestamp\
                (now() - interval'30 min',now()) 
            where unique_query_id= {unique_sql_id} and lock_wait_count>0;'''
        sql_msg = self.sh_primy.execut_db_sql(sql_cmd, dbname='postgres')
        self.logger.info(sql_msg)
        self.common.equal_sql_mdg(sql_msg, 'pg_sleep ', '----------', ' ', '(1 row)', '', ' ?column? | ?column? ',
                                  '----------+----------', ' t        | t', '(1 row)')

    def tearDown(self):
        text_7 = '---step7: 环境清理,expect:成功:---'
        self.logger.info(text_7)
        drop_cmd = f'drop table {self.table_name};'
        drop_msg = self.sh_primy.execut_db_sql(drop_cmd)
        self.logger.info(drop_msg)

        text = '---检查参数，恢复原值:track_stmt_session_slot---'
        self.logger.info(text)
        check_slot = self.sh_primy.execut_db_sql(self.show_slot_cmd)
        self.logger.info(check_slot)
        current = check_slot.splitlines()[-2].strip()
        if self.slot_default != current:
            result = self.sh_primy.execute_gsguc('reload',
                                                 self.constant.GSGUC_SUCCESS_MSG,
                                                 f"{self.config_slot}='{self.slot_default}'")
            self.assertTrue(result, '参数恢复失败')
        show_slot_msg = self.sh_primy.execut_db_sql(self.show_slot_cmd)
        self.logger.info(show_slot_msg)

        self.logger.info(
            '----检查参数，恢复默认配置:rack_stmt_stat_level----')
        check_level_res = self.sh_primy.execut_db_sql(self.show_level_cmd)
        self.logger.info(check_level_res)
        current = check_level_res.splitlines()[-2].strip()
        if self.level_default != current:
            result = self.sh_primy.execute_gsguc('reload',
                                                 self.constant.GSGUC_SUCCESS_MSG,
                                                 f"{self.config_level}='{self.level_default}'")
            self.assertTrue(result, '参数恢复失败')
        show_level_msg = self.sh_primy.execut_db_sql(self.show_level_cmd)
        self.logger.info(show_level_msg)
        self.common.equal_sql_mdg(show_slot_msg, f'{self.config_slot}',
                                  f'{self.slot_default}', '(1 row)', flag='1')
        self.common.equal_sql_mdg(show_level_msg, f'{self.config_level}',
                                  f'{self.level_default}', '(1 row)', flag='1')
        self.assertIn(self.constant.DROP_TABLE_SUCCESS,
                      drop_msg, '执行失败:' + text_7)
        self.logger.info(
            '---Opengauss_Function_Fullsql_Case0009 finish---')
